﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using MySql.Data.MySqlClient;

namespace REFWebService
{
    /// <summary>
    /// Summary description for database
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    /// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    /// [System.Web.Script.Services.ScriptService]
    public class database : System.Web.Services.WebService
    {
        private string connectionString = "Server=localhost;" +
                                          "Database='ref_web';" +
                                          "User='root';" +
                                          "Password='booger';";
        private MySqlConnection conn;
                                        
        [WebMethod]
        ///Insert the new ratings into the web database
        public int saveRating(int user_id, int location_id, int rating)
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();
            String query = "insert into location_ratings (location_id, user_id, rating, created_time, edited_time) values (" +
                           location_id + ", " + user_id + ", " + rating + ",'" + convertDateTime(DateTime.Now) + "', '" + convertDateTime(DateTime.Now) + "');";
            MySqlCommand cmd = new MySqlCommand(query, conn);
            cmd.ExecuteScalar();
            conn.Close();
            return 1;
        }
        /// <summary>
        /// insert the new location comment into the web database
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="location_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>

        [WebMethod]
        public int saveLocationComment(int user_id, int location_id, String text)
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();
            String query = "insert into location_comments (location_id, user_id, text, created_time, edited_time) values (" +
                           location_id + ", " + user_id + ", '" + text + "','" + convertDateTime(DateTime.Now) + "', '" + convertDateTime(DateTime.Now) + "');";
            MySqlCommand cmd = new MySqlCommand(query, conn);
            cmd.ExecuteScalar();
            conn.Close();
            return 1;
        }
        /// <summary>
        /// save the event comment to the web database
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        [WebMethod]
        public int saveEventComment(int user_id, int event_id, String text)
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();
            String query = "insert into event_comments (event_id, user_id, text, created_time, edited_time) values (" +
                           event_id + ", " + user_id + ", '" + text + "','" + convertDateTime(DateTime.Now) + "', " + "'" + convertDateTime(DateTime.Now) + "');";
            MySqlCommand cmd = new MySqlCommand(query, conn);
            cmd.ExecuteScalar();
            conn.Close();
            return 1;
        }

        [WebMethod]
        /// <summary>
        /// save the user comment to the web database
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        public int saveUser(String username, String password)
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();
            String query = "insert into users (username, password, created_time, edited_time) values ('" +
                           username + "', '" + password + "', '" + convertDateTime(DateTime.Now) + "', " + "'" + convertDateTime(DateTime.Now) + "');";
            MySqlCommand cmd = new MySqlCommand(query, conn);
            cmd.ExecuteScalar();
            conn.Close();
            return 1;
        }

        [WebMethod]
        /// <summary>
        /// save the rsvp to the web database
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        public int saveRSVP(int user_id, int event_id)
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();
            String query = "select 1 from user_events where event_id = " + event_id + " and user_id = " + user_id;
            MySqlCommand cmd = new MySqlCommand(query, conn);
            MySqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
            {
                reader.Close();
                query = "insert into user_events (event_id, user_id, created_time, edited_time) values (" +
                               event_id + ", " + user_id + ", '" + convertDateTime(DateTime.Now) + "', '" + convertDateTime(DateTime.Now) + "');";
                cmd = new MySqlCommand(query, conn);
                cmd.ExecuteScalar();
            }
            conn.Close();
            return 1;
        }

        [WebMethod]
        /// <summary>
        /// save the event to the web database given all the attributes of 
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        public int saveEvent(int location_id, string website, string description, string price, string name, string phone, string email, string image_url, DateTime start_time, DateTime end_time)
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();
            String query = "insert into events (location_id, website, description, price, name, phone, email, image_url, start_time, end_time, created_time, edited_time) values (" +
                           location_id + ", '" + website + "', '" + description + "', '" + price + "', '" + name + "', '" + phone + "', '" + email + "', '" + image_url + "', '" + convertDateTime(start_time) + "', '" + convertDateTime(end_time) + "', '" + convertDateTime(DateTime.Now) + "', '" + convertDateTime(DateTime.Now) + "');";
            MySqlCommand cmd = new MySqlCommand(query, conn);
            cmd.ExecuteScalar();
            conn.Close();
            return 1;
        }

        [WebMethod]
        /// <summary>
        /// save the event category to the web database given the event id and category id
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        public int saveEventCategory(int event_id, int category_id)
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();
            String query = "select 1 from event_categories where event_id = " + event_id + " and category_id = " + category_id;
            MySqlCommand cmd = new MySqlCommand(query, conn);
            MySqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
            {
                reader.Close();
                query = "insert into event_categories (event_id, category_id, created_time, edited_time) values (" +
                           event_id + ", " + category_id + ", '" + convertDateTime(DateTime.Now) + "', '" + convertDateTime(DateTime.Now) + "');";
                cmd = new MySqlCommand(query, conn);
                cmd.ExecuteScalar();
            }
            conn.Close();
            return 1;
        }

        [WebMethod]
        /// <summary>
        /// get all the data from the database that was created after the last update
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        public String sync(DateTime lastUpdated)
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();

            String results = getNewUsers(lastUpdated);
            results += "\n" + getNewCategories(lastUpdated);
            results += "\n" + getNewLocations(lastUpdated);
            results += "\n" + getNewLocationCategories(lastUpdated);
            results += "\n" + getNewLocationComments(lastUpdated);
            results += "\n" + getNewLocationRatings(lastUpdated);
            results += "\n" + getNewEvents(lastUpdated);
            results += "\n" + getNewEventCategories(lastUpdated);
            results += "\n" + getNewEventComments(lastUpdated);
            results += "\n" + getNewUserEvents(lastUpdated);

            conn.Close();
            return results;
        }
        /// <summary>
        /// converts a .net datatime into mySQL format
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>

        private String convertDateTime(DateTime t)
        {
            String result = "";
            result += t.Year.ToString() + "-";
            result += t.Month.ToString() + "-";
            result += t.Day.ToString() + " ";
            result += t.Hour.ToString() + ":";
            result += t.Minute.ToString() + ":";
            result += t.Second.ToString();
            return result;
        }
        /// <summary>
        /// get all the categories created after the last update time
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewCategories(DateTime lastUpdated)
        {
            String query = "select * from categories c where c.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into categories (category_id, name, parent_category_id, created_time, edited_time) values (";
            return getNewData(query, insertCode);
        }
        /// <summary>
        /// get all the locations created after the last update time
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewLocations(DateTime lastUpdated)
        {
            String query = "select * from locations l where l.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into locations (location_id, name, description, website, phone, fax, email, street_address1, street_address2, city, state, zipcode, image_url, created_time, edited_time) values (";
            return getNewData(query, insertCode);
        }
        /// <summary>
        /// get the location categories that have been added since the last update
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewLocationCategories(DateTime lastUpdated)
        {
            String query = "select * from location_categories lc where lc.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into location_categories (location_id, category_id, created_time, edited_time) values (";
            return getNewData(query, insertCode);
        }
        /// <summary>
        /// get the users that have been added to the system since the last update
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewUsers(DateTime lastUpdated)
        {
            String query = "select * from users u where u.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into users (user_id, username, password, created_time, edited_time) values (";
            return getNewData(query, insertCode);
        }
        /// <summary>
        /// get the new location comments that have been added since the last update
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewLocationComments(DateTime lastUpdated)
        {
            String query = "select * from location_comments lc where lc.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into location_comments (location_comment_id, location_id, text, user_id, created_time, edited_time) values (";
            return getNewData(query, insertCode);
        }
        /// <summary>
        /// get new events that have been added to the database since the last update
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewEvents(DateTime lastUpdated)
        {
            String query = "select * from events e where e.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into events (event_id, location_id, website, description, price, name, phone, email, image_url, created_time, edited_time, start_time, end_time) values (";
            return getNewData(query, insertCode);
        }
        /// <summary>
        /// get the new event categories that have been added since the last update
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewEventCategories(DateTime lastUpdated)
        {
            String query = "select * from event_categories ec where ec.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into event_categories (event_id, category_id, created_time, edited_time) values (";
            return getNewData(query, insertCode);
        }
        /// <summary>
        /// get the new event comments that have been added since the last update
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewEventComments(DateTime lastUpdated)
        {
            String query = "select * from event_comments ec where ec.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into event_comments (event_comment_id, event_id, text, user_id, created_time, edited_time) values (";
            return getNewData(query, insertCode);
        }
        /// <summary>
        /// get the location ratings that have been added since the last update
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewLocationRatings(DateTime lastUpdated)
        {
            String query = "select * from location_ratings lr where lr.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into location_ratings (rating_id, location_id, user_id, rating, created_time, edited_time) values (";
            return getNewData(query, insertCode);
        }
        /// <summary>
        /// get the new user events that have been added since the last update
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="event_id"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        private String getNewUserEvents(DateTime lastUpdated)
        {
            String query = "select * from user_events ue where ue.created_time > '" + convertDateTime(lastUpdated) + "';";
            String insertCode = "insert into user_events (event_id, user_id, created_time, edited_time) values (";
            return getNewData(query, insertCode);
        }
        
        private String getNewData(String query, String insertCode)
        {
            MySqlCommand cmd = new MySqlCommand(query, conn);
            MySqlDataReader reader = cmd.ExecuteReader();
            String inserts = "";
            while (reader.Read())
            {
                String q = insertCode;
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    if (reader.GetValue(i).ToString() == "")
                    {
                        q += "null, ";
                    }
                    else if (reader.GetValue(i).GetType() == typeof(String))
                    {
                        q += "'" + reader.GetValue(i).ToString() + "', ";
                    }
                    else if (reader.GetValue(i).GetType() == typeof(DateTime))
                    {
                        q += "'" + convertDateTime((DateTime)reader.GetValue(i)) + "', ";
                    }
                    else
                    {
                        q += reader.GetValue(i).ToString() + ", ";
                    }

                }
                q = q.Substring(0, q.Length - 2);
                q += ")";
                inserts += q + "\n";
            }
            if (inserts.Length > 0)
            {
                inserts = inserts.Substring(0, inserts.Length - 1);
            }
            reader.Close();
            return inserts;
        }
    }
}
